/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package maindatart.metadata;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;
import maindatart.util.C3p0Source;

/**
 *
 * @author 丁丁
 */
public class MetaDataDao {
    
    
    List<MainDataMetaDataEntity> getAll() throws SQLException{
        List<MainDataMetaDataEntity> list = new ArrayList();
        
        
        String sql = "select id,name,en_name,cobon from maindata_metadata ";
        Connection cn = C3p0Source.getConnection();
        
        
        Statement d = null;
        ResultSet rs = null;
        try {
             d = cn.createStatement();
             rs = d.executeQuery(sql);
             
             while(rs.next()){
                 MainDataMetaDataEntity mm = new MainDataMetaDataEntity();
                 mm = getDataFromResultSet(rs);
                 list.add(mm);
                 
             }
             
        } catch (SQLException ex) {
            Logger.getLogger(MetaDataDao.class.getName()).log(Level.SEVERE, null, ex);
            throw ex;
        }finally{
            C3p0Source.realseSource(rs,d,cn);
        }
       
        for(MainDataMetaDataEntity mmd : list ){
            String id = mmd.getID();
            List<MainDataPropertyEntity> mdpelist = this.getMainDataPropertyEntity(id);
            mmd.setProperties(mdpelist);
    }
        
        
        return list;
    }
    
    public MainDataMetaDataEntity getMainDataMetaDataByID(String id) throws SQLException{
        String sql = "select id,name,en_name,cobon from maindata_metadata where id=?";
        Connection cn = C3p0Source.getConnection();
        
        
        PreparedStatement ps = null;
        ResultSet rs = null;
        MainDataMetaDataEntity mm = new MainDataMetaDataEntity();
        try {
             ps = cn.prepareStatement(sql);
             ps.setString(1, id);
             rs = ps.executeQuery();
             
             if(rs.next()){
                 
                 mm = getDataFromResultSet(rs);
                 List<MainDataPropertyEntity> mdpelist = this.getMainDataPropertyEntity(id);
                mm.setProperties(mdpelist);
             }
             
        } catch (SQLException ex) {
            Logger.getLogger(MetaDataDao.class.getName()).log(Level.SEVERE, null, ex);
            throw ex;
        }finally{
            C3p0Source.realseSource(rs,ps,cn);
        }
        return mm;
    }
    
        public MainDataMetaDataEntity getMainDataMetaDataByEnName(String enName) throws SQLException{
        String sql = "select id,name,en_name,cobon from maindata_metadata where en_name=?";
        Connection cn = C3p0Source.getConnection();
        
        
        PreparedStatement ps = null;
        ResultSet rs = null;
        MainDataMetaDataEntity mm = new MainDataMetaDataEntity();
        try {
             ps = cn.prepareStatement(sql);
             ps.setString(1, enName);
             rs = ps.executeQuery();
             
             if(rs.next()){
                 
                 mm = getDataFromResultSet(rs);
                 List<MainDataPropertyEntity> mdpelist = this.getMainDataPropertyEntity(mm.getID());
                mm.setProperties(mdpelist);
             }
             
        } catch (SQLException ex) {
            Logger.getLogger(MetaDataDao.class.getName()).log(Level.SEVERE, null, ex);
            throw ex;
        }finally{
            C3p0Source.realseSource(rs,ps,cn);
        }
        return mm;
    }
    
    /**
     * 根据外键获取子表数据
     * @param mdmdid
     * @return 
     */
    private List<MainDataPropertyEntity> getMainDataPropertyEntity(String mdmdid) throws SQLException{
        List<MainDataPropertyEntity> list = new ArrayList<MainDataPropertyEntity>();
        
        
        String sql = "select mdpid,name,datatype,length from md_propertity where mdepid=?";
        Connection cn = C3p0Source.getConnection();
        
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
             ps = cn.prepareStatement(sql);
             ps.setString(1, mdmdid);
             rs = ps.executeQuery();
             
             while(rs.next()){
                 MainDataPropertyEntity mdpe = new MainDataPropertyEntity();
                 mdpe.setMdp_id(rs.getString("mdpid"));
                 mdpe.setDatatype(rs.getString("datatype"));
                 mdpe.setLength(rs.getInt("length"));
                 mdpe.setName(rs.getString("name"));
                 mdpe.setNullable(false);
                 list.add(mdpe);
             }
             
        } catch (SQLException ex) {
            Logger.getLogger(MetaDataDao.class.getName()).log(Level.SEVERE, null, ex);
            throw ex;
        }finally{
            C3p0Source.realseSource(rs,ps,cn);
            
        }
        
        
        return list;
    }
    
    public void addMainDataMetaData(MainDataMetaDataEntity mdmd) throws SQLException{
        String sql = "insert into maindata_metadata(id,name,en_name,cobon) values(?,?,?,?) ";
        String mainID = UUID.randomUUID().toString();
        
        Connection cn = C3p0Source.getConnection();
        
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
             ps = cn.prepareStatement(sql);
             ps.setString(1, mainID);
             ps.setString(2, mdmd.getMainDataName());
             ps.setString(3, mdmd.getEnName());
             ps.setInt(4, mdmd.getCarbonDataCount());
             
             ps.execute();
             
             List<MainDataPropertyEntity> list = mdmd.getProperties();
             for(MainDataPropertyEntity pe : list){
                String sql1 = "insert into md_propertity(mdpid,mdepid,name,datatype,length) values(?,?,?,?,?)";
                ps = cn.prepareStatement(sql1);
                
                ps.setString(1, UUID.randomUUID().toString());
                ps.setString(2, mainID);
                ps.setString(3, pe.getName());
                ps.setString(4, pe.getDatatype());
                ps.setInt(5, pe.getLength());
             ps.execute();
                 
             }
             
        } catch (SQLException ex) {
            Logger.getLogger(MetaDataDao.class.getName()).log(Level.SEVERE, null, ex);
           
             throw ex;
        }finally{
            C3p0Source.realseSource(rs,ps,cn);
            
        }
    }
    /**
     *
     * @param mdmd
     */
    public void dellMainDataMetaDatas(MainDataMetaDataEntity mdmd) throws SQLException {
             String sql = "delete maindata_metadata where id = ? ";
            String mainID = UUID.randomUUID().toString();

            Connection cn = C3p0Source.getConnection();

            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                ps = cn.prepareStatement(sql);
                ps.setString(1, mdmd.getID());
                ps.execute();

                String sql1 = "delete md_propertity where mdpid=?";
                ps = cn.prepareStatement(sql);
                ps.setString(1,mdmd.getID());
                ps.execute();

            } catch (SQLException ex) {
                Logger.getLogger(MetaDataDao.class.getName()).log(Level.SEVERE, null, ex);
                throw ex;
            }finally{
                C3p0Source.realseSource(rs,ps,cn);

            }
        }
          
    private MainDataMetaDataEntity getDataFromResultSet(ResultSet rs) throws SQLException{
          MainDataMetaDataEntity mm = new MainDataMetaDataEntity();
                 mm.setID(rs.getString("id"));
                 mm.setEnName(rs.getString("en_name"));
                 mm.setMainDataName(rs.getString("name"));
                 mm.setCarbonDataCount(rs.getInt("cobon"));
                 
                 return mm;
    }
    
    public static void main(String args[]) throws Exception{
        try{
            throw new Exception("eee");
        }catch(Exception e){
            throw e;
        }finally{
            System.out.println("hei");
        }
        
    }
}
